from fastapi import APIRouter, Depends, HTTPException
from typing import List, Optional
from pydantic import BaseModel, ValidationError
from datetime import datetime, timezone, timedelta
from sqlalchemy.orm import Session
from sqlalchemy import or_
from ..database import get_db
from ..models.shop import Product, Shop, Category, CategoryResponse, BaseConfiguration
from backend.sku import models # ItemSKU model
from backend.data_clean.config_manager import config_manager
from backend.data_clean.models import UnitConversion, MarketingWordConfig, BrandConfig # Import necessary models
from .auth import get_current_active_user
import asyncio
import logging # 导入 logging
import re # Import re module
import json # Import json module
from backend.data_clean.api_service import call_deepseek_api
import wmi
from urllib.parse import urlparse, parse_qsl, urlencode, quote, unquote
import requests
import time
import uuid
from backend.wingknife.models import WingknifeExecutionRecord
import httpx
import re

logger = logging.getLogger(__name__) # 获取 logger 实例

router = APIRouter(
    tags=["shop"],
    responses={404: {"description": "Not found"}},
)

class ProductBase(BaseModel):
    item_name: str
    item_brand: Optional[str] = None
    item_specs: Optional[str] = None
    item_price: float
    item_classification: Optional[str] = None
    base_price: Optional[float] = None
    base_unit: Optional[str] = None
    shop_id: int
    shop_name: str
    shop_store_id: int
    sku_id: str
    item_img: Optional[str] = None # item_img 设置为 Optional
    run_id: str
    created_at: Optional[datetime] = None


class ProductCreate(ProductBase):
    pass

class ProductResponse(ProductBase):
    id: int
    created_at: datetime

    class Config:
        from_attributes = True

class ShopBase(BaseModel):
    store_id: Optional[int] = None
    shop_name: Optional[str] = None
    shipping_address: Optional[str] = None
    logo_img: Optional[str] = None
    shop_address: Optional[str] = None
    longitude: Optional[str] = None
    latitude: Optional[str] = None
    provincia_level_name: Optional[str] = None
    municipa_level_name: Optional[str] = None
    district_level_name: Optional[str] = None
    region_name: Optional[str] = None
    enabled: Optional[str] = None
    final_exec_timestamp: Optional[datetime] = None
    item_count: Optional[int] = None
    present_state: Optional[str] = None

class ShopCreate(ShopBase):
    pass

class ShopResponse(ShopBase):
    id: int
    created_at: Optional[datetime] = None

    class Config:
        from_attributes = True

class ShopUpdateBase(BaseModel):
    store_id: Optional[int] = None
    shop_name: Optional[str] = None
    logo_img: Optional[str] = None
    shop_address: Optional[str] = None
    longitude: Optional[str] = None
    latitude: Optional[str] = None
    provincia_level_name: Optional[str] = None
    municipa_level_name: Optional[str] = None
    district_level_name: Optional[str] = None
    region_name: Optional[str] = None
    enabled: Optional[str] = None

    class Config:
        from_attributes = True

class CategoryCreate(BaseModel):
    category_id: str
    category_name: str
    first_category_id: Optional[str] = None
    first_category_name: Optional[str] = None
    category_type: Optional[str] = None

class BaseConfigurationBase(BaseModel):
    login_id: Optional[str] = None
    device_id: Optional[str] = None
    v: Optional[str] = None
    app_version: Optional[str] = None
    request_version: Optional[str] = None
    free_version: Optional[str] = None
    session_id: Optional[str] = None
    uuid: Optional[str] = None

class BaseConfigurationCreate(BaseConfigurationBase):
    pass

class BaseConfigurationResponse(BaseConfigurationBase):
    id: int
    created_at: Optional[datetime] = None

    class Config:
        from_attributes = True

def ai_label_sku(item_names: List[str],item_type: str, config_manager) -> List[dict]:
    """
    使用 DeepSeek API 进行智能标注和分词
    
    Args:
        item_names: 单个商品名称或商品名称列表
        config_manager: 配置管理器
        
    Returns:
        始终返回List[dict]，即使是单个商品也返回长度为1的列表
    """
    try:
        # 统一转换为列表处理
        if isinstance(item_names, str):
            item_names = [item_names]
        
        # 对空值和无效值进行过滤
        valid_names = [name.strip() for name in item_names if name and isinstance(name, str) and name.strip()]
        if not valid_names:
            logger.warning("没有有效的商品名称需要处理")
            return []
            
        # 构建批量处理的提示词
        items_text = "\n".join([f"商品{i+1}：{name}" for i, name in enumerate(valid_names)])
        prompt = f"""请分析以下商品名称列表，按顺序提取每个商品的关键信息并进行分词。
本次商品的类型为{item_type}，下面我将给出额外需要注意的商品类型的注意事项，若本次的商品类型存在注事项，请在处理时额外注意：
蔬菜豆制品：此类商品中，蔬菜无特殊说明则为散装，豆制品一律按照非散装处理
酒水饮料：此类商品包装类型全部视为非散装商品
粮油调味：此类商品包装类型全部视为非散装商品
分析前，请先将促销、热门等类似的无用字段，以及由【】或[]两种括号即文字信息先去除。对于每个商品，需要识别：
1. 包装单位：判断是盒、包、袋、瓶还是散装。如果没有明确的包装单位，则视为散装。
2. 品牌名称：提取商品的品牌。
3. 规格信息：商品信息中容量或重量的数值及单位。
4. 基础单位：根据当前商品的包装单位，判断其最小拆分单位如果包装单位非散装，则其单位不会为g、kg、毫升等标准单位，若为散装，则其单位为g、kg、毫升等标准单位。
5. 清洗后的名称：去除营销词、规格等信息后的核心商品名。
6. 数量：当前商品根据基础单位可以拆分出几个。若其基础单位为g、kg等标准单位，其数量按单位量来（如200kg，则其为200），如瓶、袋等非标准单位否则按其数量来，如500g*10，则为10，若无法判断则默认为1。
7、未处理前的信息：未经任何处理的信息

商品列表：
{items_text}

处理过后请对每个商品的部分信息进行检查，进行二次处理。请对每个处理后的商品信息依次进行如下检查：
1、包装单位：该信息若非"散装"，则只允许为一个汉字，请在最初处理后根据包装单位提取，或根据最初处理后的包装单位判断
2、基础单位：若包装单位非"散装"但本信息为基础单位（g、kg、毫升等），则此次信息为与包装单位一致
3、数量：基础单位二次处理发生变化后，重新判断数量
4、清洗后的名称：是否为明确商品（不明确的商品："组合装"、"调味料"等），若不是则根据原信息去除品牌名称和规格作为商品名称

请以JSON数组格式返回分析结果，每个商品对应一个对象：
[
    {{
        "package_type": "盒/包/袋/瓶/散装",
        "brand": "品牌名称",
        "specs": "规格信息",
        "base_unit": "基础单位",
        "cleaned_name": "清洗后名称",
        "base_num": "数量",
        "item_name": "未处理前的信息",
        "confidence": 0.95
    }}
]

只返回JSON格式数据，不要其他说明文字。"""

        # 添加重试机制
        max_retries = 3
        retry_count = 0
        
        while retry_count < max_retries:
            try:
                # 从配置管理器获取API密钥
                api_key = None
                if config_manager and hasattr(config_manager, '_is_initialized') and config_manager._is_initialized:
                    if hasattr(config_manager, '_system_config'):
                        api_key = config_manager._system_config.get("DEEPSEEK_API_KEY")
                        if not api_key:
                            logger.error("未能获取DEEPSEEK_API_KEY，请在配置管理器、环境变量或.env文件中设置")
                            return None

                # 调用 DeepSeek API
                logger.info(f"开始调用AI标注服务，处理 {len(valid_names)} 个商品")
                response = call_deepseek_api(prompt, config_manager)
                
                if response and response.get('choices') and len(response['choices']) > 0:
                    content = response['choices'][0]['message']['content']
                    # 解析返回的 JSON
                    try:
                        results = json.loads(content)
                        # 确保返回的是列表类型
                        if not isinstance(results, list):
                            logger.error(f"AI 返回结果格式错误，期望列表但得到: {type(results)}")
                            retry_count += 1
                            continue
                            
                        # 验证结果数量是否匹配
                        if len(results) != len(valid_names):
                            logger.warning(f"AI返回结果数量({len(results)})与输入数量({len(valid_names)})不匹配")
                            # 如果结果数量少于输入，补充空结果
                            while len(results) < len(valid_names):
                                results.append({
                                    "package_type": "件",
                                    "brand": "",
                                    "specs": "",
                                    "base_unit": "件",
                                    "cleaned_name": valid_names[len(results)],
                                    "base_num": 1.0,
                                    "confidence": 0.5
                                })
                        
                        # 验证每个结果的格式
                        for i, result in enumerate(results):
                            if not isinstance(result, dict):
                                results[i] = {
                                    "package_type": "散装",
                                    "brand": "",
                                    "specs": "",
                                    "base_unit": "",
                                    "cleaned_name": valid_names[i] if i < len(valid_names) else "",
                                    "base_num": 1.0,
                                    "confidence": 0.5
                                }
                            cleaned_info = postprocess_ai_cleaned_info(result)
                            if cleaned_info.get('success'):
                                results[i] = cleaned_info.get('data')
                        
                        return results
                        
                    except json.JSONDecodeError as e:
                        logger.error(f"AI 返回结果解析失败: {content}, 错误: {str(e)}")
                        retry_count += 1
                        continue
                
                retry_count += 1
                
            except Exception as e:
                logger.error(f"调用 AI API 失败: {str(e)}")
                retry_count += 1
                continue
        
        # 如果所有重试都失败，返回默认结果
        logger.error("AI处理失败，返回默认结果")
        return [
            {
                "package_type": "散装",
                "brand": "",
                "specs": "",
                "base_unit": "",
                "cleaned_name": name,
                "base_num": 1.0,
                "confidence": 0.5
            }
            for name in valid_names
        ]
    
    except Exception as e:
        logger.error(f"AI 标注失败: {str(e)}")
        return None
    
def ai_label_sku_lyjs(item_names: List[str], config_manager) -> List[dict]:
    """
    使用 DeepSeek API 进行智能标注和分词
    
    Args:
        item_names: 单个商品名称或商品名称列表
        config_manager: 配置管理器
        
    Returns:
        始终返回List[dict]，即使是单个商品也返回长度为1的列表
    """
    try:
        # 统一转换为列表处理
        if isinstance(item_names, str):
            item_names = [item_names]
        
        # 对空值和无效值进行过滤
        valid_names = [name.strip() for name in item_names if name and isinstance(name, str) and name.strip()]
        if not valid_names:
            logger.warning("没有有效的商品名称需要处理")
            return []
            
        # 构建批量处理的提示词
        items_text = "\n".join([f"商品{i+1}：{name}" for i, name in enumerate(valid_names)])
        prompt = f"""请分析以下商品名称列表，按顺序提取每个商品的关键信息并进行分词。
分析前，请先将促销、热门等类似的无用字段，以及由【】或[]两种括号即文字信息先去除。对于每个商品，需要识别：
1. 包装单位：根据商品名称，判断其是盒、包、袋、瓶，一般饮品为瓶、粮食为袋、调味品为盒、油盐酱醋小容量为瓶大容量为桶。默认为件
2. 品牌名称：提取商品的品牌。
3. 规格信息：商品信息中容量或重量的数值及单位。
4. 基础单位：根据当前商品的包装单位，判断其最小拆分单位。默认为件。
5. 清洗后的名称：去除营销词、规格等信息后的核心商品名。
6. 数量：当前商品根据基础单位可以拆分出几个。若无法判断则默认为1，且单位为件时该信息必定为1。
7、未处理前的信息：未经任何处理的信息

商品列表：
{items_text}

处理过后请对每个商品的部分信息进行检查，进行二次处理。请对每个处理后的商品信息依次进行如下检查：
1、包装单位：该信息只允许为一个汉字，请在最初处理后根据包装单位提取，或根据最初处理后的包装单位判断
2、基础单位：若包装单位非"散装"但本信息为标准单位（g、kg、毫升等），则此次信息为与包装单位一致
3、数量：基础单位二次处理发生变化后，重新判断数量
4、清洗后的名称：是否为明确商品（不明确的商品："组合装"、"调味料"等），若不是则根据原信息去除品牌名称和规格作为商品名称

请以JSON数组格式返回分析结果，每个商品对应一个对象：
[
    {{
        "package_type": "盒/包/袋/瓶",
        "brand": "品牌名称",
        "specs": "规格信息",
        "base_unit": "基础单位",
        "cleaned_name": "清洗后名称",
        "base_num": "数量",
        "item_name": "未处理前的信息",
        "confidence": 0.95
    }}
]

只返回JSON格式数据，不要其他说明文字。"""

        # 添加重试机制
        max_retries = 3
        retry_count = 0
        
        while retry_count < max_retries:
            try:
                # 从配置管理器获取API密钥
                api_key = None
                if config_manager and hasattr(config_manager, '_is_initialized') and config_manager._is_initialized:
                    if hasattr(config_manager, '_system_config'):
                        api_key = config_manager._system_config.get("DEEPSEEK_API_KEY")
                        if not api_key:
                            logger.error("未能获取DEEPSEEK_API_KEY，请在配置管理器、环境变量或.env文件中设置")
                            return None

                # 调用 DeepSeek API
                logger.info(f"开始调用AI标注服务，处理 {len(valid_names)} 个商品")
                response = call_deepseek_api(prompt, config_manager)
                
                if response and response.get('choices') and len(response['choices']) > 0:
                    content = response['choices'][0]['message']['content']
                    # 解析返回的 JSON
                    try:
                        results = json.loads(content)
                        # 确保返回的是列表类型
                        if not isinstance(results, list):
                            logger.error(f"AI 返回结果格式错误，期望列表但得到: {type(results)}")
                            retry_count += 1
                            continue
                            
                        # 验证结果数量是否匹配
                        if len(results) != len(valid_names):
                            logger.warning(f"AI返回结果数量({len(results)})与输入数量({len(valid_names)})不匹配")
                            # 如果结果数量少于输入，补充空结果
                            while len(results) < len(valid_names):
                                results.append({
                                    "package_type": "件",
                                    "brand": "",
                                    "specs": "",
                                    "base_unit": "件",
                                    "cleaned_name": valid_names[len(results)],
                                    "base_num": 1.0,
                                    "confidence": 0.5
                                })
                        
                        # 验证每个结果的格式
                        for i, result in enumerate(results):
                            if not isinstance(result, dict):
                                results[i] = {
                                    "package_type": "件",
                                    "brand": "",
                                    "specs": "",
                                    "base_unit": "",
                                    "cleaned_name": valid_names[i] if i < len(valid_names) else "",
                                    "base_num": 1.0,
                                    "confidence": 0.5
                                }
                            cleaned_info = postprocess_ai_cleaned_info(result)
                            if cleaned_info.get('success'):
                                results[i] = cleaned_info.get('data')
                        
                        return results
                        
                    except json.JSONDecodeError as e:
                        logger.error(f"AI 返回结果解析失败: {content}, 错误: {str(e)}")
                        retry_count += 1
                        continue
                
                retry_count += 1
                
            except Exception as e:
                logger.error(f"调用 AI API 失败: {str(e)}")
                retry_count += 1
                continue
        
        # 如果所有重试都失败，返回默认结果
        logger.error("AI处理失败，返回默认结果")
        return [
            {
                "package_type": "散装",
                "brand": "",
                "specs": "",
                "base_unit": "",
                "cleaned_name": name,
                "base_num": 1.0,
                "confidence": 0.5
            }
            for name in valid_names
        ]
    
    except Exception as e:
        logger.error(f"AI 标注失败: {str(e)}")
        return None

"""
创建新商品
---
参数:
- product: 商品创建模型，包含名称、描述、价格、分类ID、库存和店铺ID
- db: 数据库会话
- current_user: 当前登录用户

返回:
- 201: 创建成功，返回商品详情
- 400: 创建失败，输入数据有误
"""
@router.post("/products/", response_model=ProductResponse, status_code=201)
async def create_product(
    product: ProductCreate,
    db: Session = Depends(get_db),
    current_user = Depends(get_current_active_user)
):
    db_product = Product(**product.dict())
    db.add(db_product)
    try:
        db.commit()
        db.refresh(db_product)
        return db_product
    except Exception as e:
        db.rollback()
        raise HTTPException(status_code=400, detail="商品创建失败，请检查输入数据")
    
"""
批量新增商品
---
参数:
- products: 商品创建模型列表
- db: 数据库会话
- current_user: 当前登录用户

"""
@router.post("/products/batch", response_model=List[ProductResponse], status_code=201)
def create_products(
    products: List[ProductCreate],
    db: Session = Depends(get_db),
    current_user = Depends(get_current_active_user)
):
    """批量处理商品数据"""
    try:
        # 确保配置管理器已初始化
        if not hasattr(config_manager, '_is_initialized') or not config_manager._is_initialized:
            config_manager.initialize(db)
            logger.info("配置管理器已初始化")
        
        # 获取营销词和品牌配置
        marketing_words = config_manager.marketing_words
        brands = config_manager.brands
        
        db_products = []
        products_need_ai_label = []

        # 只生成一次，放循环外
        today_start = datetime.now(timezone.utc).replace(hour=0, minute=0, second=0, microsecond=0)
        today_end = today_start + timedelta(days=1) - timedelta(microseconds=1)

        for product_data in products:
            # 创建商品实例
            if hasattr(product_data, 'dict'):
                new_item = Product(**product_data.dict())
            elif isinstance(product_data, dict):
                new_item = Product(**product_data)
            else:
                new_item = Product(**product_data)

            logger.info(f"开始处理商品: {new_item.item_name}, sku_id: {new_item.sku_id}, shop_store_id: {new_item.shop_store_id}")

            existing_product = None
            # 查询是否已存在今日相同SKU和店铺的商品（注意：此处用 models.Item）
            existing_product = db.query(models.Item).filter(
                models.Item.sku_id == new_item.sku_id,
                models.Item.shop_store_id == new_item.shop_store_id,
                models.Item.updated_at >= today_start,
                models.Item.updated_at <= today_end
            ).first()

            logger.info(f"查询今日已存在商品: {new_item.item_name}, 找到记录: {existing_product is not None}")

            if existing_product:
                logger.info(f"商品 {new_item.item_name} 今日已存在，跳过处理")
                # # 用 new_item 的数据更新 existing_product（保留 id/created_at）
                # for field in new_item.__table__.columns.keys():
                #     if field not in ['id', 'created_at']:
                #         setattr(existing_product, field, getattr(new_item, field))
                # existing_product.updated_at = datetime.now(timezone.utc)
                # db_products.append(existing_product)
                db.expunge(new_item)
                continue
            
            logger.info(f"商品 {new_item.item_name} 今日不存在，继续处理")
            
            # 只有不存在时才添加到数据库和列表中
            db.add(new_item)
            db_products.append(new_item)

            # 根据 sku_id 同步 item_sku 信息
            logger.info(f"检查商品 {new_item.item_name} 的SKU ID: {new_item.sku_id}")
            if new_item.sku_id:
                # 首先查询是否存在已处理的SKU记录
                matching_sku = db.query(models.ItemSKU).filter(
                    models.ItemSKU.sku == new_item.sku_id,
                    models.ItemSKU.is_handled == 1,
                ).first()
                logger.info(f"查询已处理的SKU记录 {new_item.sku_id}，找到记录: {matching_sku is not None}")

                # 如果没有找到已处理的SKU，检查是否存在未处理的SKU
                if not matching_sku:
                    unhandled_sku = db.query(models.ItemSKU).filter(
                        models.ItemSKU.sku == new_item.sku_id,
                        models.ItemSKU.is_handled == 0,
                    ).first()
                    logger.info(f"查询未处理的SKU记录 {new_item.sku_id}，找到记录: {unhandled_sku is not None}")
                    if unhandled_sku:
                        logger.info(f"发现未处理的SKU记录 {new_item.sku_id}，将重新进行AI处理")
                        # 从会话中移除商品对象，避免后续刷新时出错
                        db.expunge(new_item)
                        # 从db_products列表中移除，避免后续刷新时出错
                        db_products.remove(new_item)
                        # 将商品添加到AI处理列表，以便重新处理
                        products_need_ai_label.append(new_item)
                        continue

                if matching_sku:
                    new_item.cleaned_name = matching_sku.cleaned_name
                    new_item.item_brand = matching_sku.item_brand
                    new_item.item_specs = matching_sku.item_specs
                    new_item.base_unit = matching_sku.base_unit
                    new_item.package_type = matching_sku.package_type
                    new_item.base_num = matching_sku.base_num  # 添加base_num字段

                    # Calculate base_price using matching_sku.base_num
                    calculated_base_price = None
                    logger.info(f"开始计算商品 {new_item.item_name} 的基础价格，item_price: {new_item.item_price} (类型: {type(new_item.item_price)})，base_unit: {new_item.base_unit}")
                    
                    if new_item.item_price is not None and new_item.base_unit:
                        logger.info(f"商品 {new_item.item_name} 满足计算条件：item_price不为空且base_unit不为空")
                    else:
                        logger.warning(f"商品 {new_item.item_name} 不满足计算条件：item_price={new_item.item_price}，base_unit={new_item.base_unit}")
                    
                    if new_item.item_price is not None and new_item.base_unit:
                        try:
                            conversion_rule = db.query(UnitConversion).filter(
                                UnitConversion.source_unit == new_item.base_unit,
                                UnitConversion.is_active == True
                            ).first()

                            base_num = matching_sku.base_num if matching_sku.base_num is not None else 1
                            if type(base_num) == str:
                                base_num = int(base_num)
                            if base_num <= 0: # Ensure base_num is positive for division
                                 base_num = 1

                            if conversion_rule:
                                formula = conversion_rule.conversion_formula
                                try:
                                    # 将数量替换到公式中的x位置
                                    formula_with_quantity = formula.replace('x', str(base_num))
                                    # 计算基础价格：总价除以换算后的数量
                                    calculated_base_price = round(new_item.item_price / eval(formula_with_quantity), 2)
                                    new_item.base_unit = conversion_rule.target_unit
                                    logger.info(f"商品 {new_item.item_name} 使用单位换算规则，原价：{new_item.item_price}，数量：{base_num}，换算后：{calculated_base_price}，单位：斤")
                                except (ValueError, TypeError, SyntaxError) as eval_error:
                                    # 如果公式计算失败，使用默认计算方式
                                    logger.error(f"Error evaluating conversion formula {formula}: {eval_error}")
                                    calculated_base_price = round(new_item.item_price / base_num, 2)
                            else:
                                # No conversion rule found, use item_price / base_num with conversion factor of 1
                                try:
                                    # 确保数据类型正确
                                    if isinstance(new_item.item_price, str):
                                        item_price = float(new_item.item_price)
                                    else:
                                        item_price = float(new_item.item_price)
                                    
                                    if isinstance(base_num, str):
                                        base_num = float(base_num)
                                    else:
                                        base_num = float(base_num)
                                    
                                    # 确保base_num不为0
                                    if base_num <= 0:
                                        base_num = 1.0
                                        logger.warning(f"商品 {new_item.item_name} 的base_num为0或负数，设置为1.0")
                                    
                                    calculated_base_price = round(item_price / base_num, 2)
                                    logger.info(f"商品 {new_item.item_name} 未找到单位换算规则，使用默认计算，原价：{item_price}，数量：{base_num}，基础价格：{calculated_base_price}")
                                except (ValueError, TypeError, ZeroDivisionError) as calc_error:
                                    logger.error(f"计算基础价格时出错: {calc_error}, 商品: {new_item.item_name}, 价格: {new_item.item_price}, 数量: {base_num}")
                                    calculated_base_price = None

                        except Exception as e:
                            logger.error(f"Error calculating base_price for SKU {new_item.sku_id}: {e}")
                            calculated_base_price = None # Calculation failed, base_price = None

                    new_item.base_price = calculated_base_price
                    logger.info(f"商品 {new_item.item_name} 最终基础价格: {calculated_base_price}")
                else: # No matching SKU found, add to AI labeling list
                    logger.info(f"No matching SKU found for {new_item.sku_id}, adding to AI labeling list.")
                    # 检查是否存在未处理的SKU记录
                    unhandled_sku = db.query(models.ItemSKU).filter(
                        models.ItemSKU.sku == new_item.sku_id,
                        models.ItemSKU.is_handled == 0,
                    ).first()
                    if unhandled_sku:
                        logger.info(f"发现未处理的SKU记录 {new_item.sku_id} (is_handled=0)，将在AI处理后更新")
                    # 从会话中移除商品对象，避免后续刷新时出错
                    db.expunge(new_item)
                    # 从db_products列表中移除，避免后续刷新时出错
                    db_products.remove(new_item)
                    # 将商品添加到AI处理列表，以便重新处理
                    products_need_ai_label.append(new_item)
            else:
                logger.warning(f"商品 {new_item.item_name} 的SKU ID为空，跳过SKU处理")

        # 处理需要AI标注的商品
        logger.info(f"准备处理AI标注，products_need_ai_label列表长度: {len(products_need_ai_label)}")
        if products_need_ai_label:
            logger.info(f"开始批量处理 {len(products_need_ai_label)} 个需要AI标注的商品")
            
            # 获取所有需要标注的商品名称
            item_names = [product.item_name for product in products_need_ai_label]
            logger.info(f"需要AI标注的商品名称列表: {item_names}")
            
            # 批量调用AI标注服务（同步调用）
            try:
                if products_need_ai_label[0].item_classification == "酒水饮料" or products_need_ai_label[0].item_classification == "粮油调味"  or products_need_ai_label[0].item_classification == "速食冻品":
                    ai_results = ai_label_sku_lyjs(item_names, config_manager)
                else:
                    ai_results = ai_label_sku(item_names, products_need_ai_label[0].item_classification, config_manager)
                
                logger.info(f"AI标注服务调用完成，返回结果数量: {len(ai_results) if ai_results else 0}")
            except Exception as ai_error:
                logger.error(f"AI标注服务调用失败: {ai_error}")
                ai_results = None
        else:
            logger.info("没有需要AI标注的商品")
            ai_results = None
        
        if ai_results and len(ai_results) > 0:
            # 处理AI标注结果
            for product, result in zip(products_need_ai_label, ai_results):
                product.package_type = result.get("package_type", "件")
                product.item_brand = result.get("brand", "")
                product.item_specs = result.get("specs", "")
                product.base_unit = result.get("base_unit", "件")
                product.cleaned_name = result.get("cleaned_name", product.item_name)
                product.base_num = result.get("base_num", 1.0)  # 添加base_num字段，默认为1.0

                # 计算基础价格
                calculated_base_price = None
                logger.info(f"AI处理后开始计算商品 {product.item_name} 的基础价格，item_price: {product.item_price} (类型: {type(product.item_price)})，base_unit: {product.base_unit}")
                
                if product.item_price is not None and product.base_unit:
                    logger.info(f"AI处理后商品 {product.item_name} 满足计算条件：item_price不为空且base_unit不为空")
                else:
                    logger.warning(f"AI处理后商品 {product.item_name} 不满足计算条件：item_price={product.item_price}，base_unit={product.base_unit}")
                
                if product.item_price is not None and product.base_unit:
                    try:
                        conversion_rule = db.query(UnitConversion).filter(
                            UnitConversion.source_unit == product.base_unit,
                            UnitConversion.is_active == True
                        ).first()

                        base_num = product.base_num if product.base_num is not None else 1
                        if type(base_num) == str:
                            base_num = int(base_num)
                        if base_num <= 0: # Ensure base_num is positive for division
                            base_num = 1

                        if conversion_rule:
                            formula = conversion_rule.conversion_formula
                            try:
                                # 将数量替换到公式中的x位置
                                formula_with_quantity = formula.replace('x', str(base_num))
                                # 计算基础价格：总价除以换算后的数量
                                calculated_base_price = round(product.item_price / eval(formula_with_quantity), 2)
                                product.base_unit = conversion_rule.target_unit
                                logger.info(f"商品 {product.item_name} 使用单位换算规则，原价：{product.item_price}，数量：{base_num}，换算后：{calculated_base_price}，单位：斤")
                            except (ValueError, TypeError, SyntaxError) as eval_error:
                                # 如果公式计算失败，使用默认计算方式
                                logger.error(f"Error evaluating conversion formula {formula}: {eval_error}")
                                calculated_base_price = round(product.item_price / base_num, 2)
                        else:
                            # No conversion rule found, use item_price / base_num with conversion factor of 1
                            try:
                                # 确保数据类型正确
                                if isinstance(product.item_price, str):
                                    item_price = float(product.item_price)
                                else:
                                    item_price = float(product.item_price)
                                
                                if isinstance(base_num, str):
                                    base_num = float(base_num)
                                else:
                                    base_num = float(base_num)
                                
                                # 确保base_num不为0
                                if base_num <= 0:
                                    base_num = 1.0
                                    logger.warning(f"商品 {product.item_name} 的base_num为0或负数，设置为1.0")
                                
                                calculated_base_price = round(item_price / base_num, 2)
                                logger.info(f"商品 {product.item_name} 未找到单位换算规则，使用默认计算，原价：{item_price}，数量：{base_num}，基础价格：{calculated_base_price}")
                            except (ValueError, TypeError, ZeroDivisionError) as calc_error:
                                logger.error(f"计算基础价格时出错: {calc_error}, 商品: {product.item_name}, 价格: {product.item_price}, 数量: {base_num}")
                                calculated_base_price = None

                    except Exception as e:
                        logger.error(f"Error calculating base_price for {product.item_name}: {e}")
                        calculated_base_price = None

                product.base_price = calculated_base_price
                logger.info(f"AI处理后商品 {product.item_name} 最终基础价格: {calculated_base_price}")

                # 检查是否存在SKU记录
                existing_sku = db.query(models.ItemSKU).filter(
                    models.ItemSKU.sku == product.sku_id
                ).first()
                logger.info(f"AI处理后查询SKU记录 {product.sku_id}，找到记录: {existing_sku is not None}，is_handled: {existing_sku.is_handled if existing_sku else 'N/A'}")

                if existing_sku:
                    # 更新已存在的SKU记录
                    old_is_handled = existing_sku.is_handled
                    existing_sku.item_name = product.item_name
                    existing_sku.cleaned_name = product.cleaned_name
                    existing_sku.item_brand = product.item_brand
                    existing_sku.item_specs = product.item_specs
                    existing_sku.item_classification = product.item_classification
                    existing_sku.item_img = product.item_img
                    existing_sku.base_unit = result.get("base_unit", "件")
                    existing_sku.package_type = product.package_type
                    existing_sku.base_num = product.base_num  # 添加base_num字段
                    existing_sku.is_handled = 1
                    existing_sku.updated_at = datetime.now()
                    logger.info(f"更新已存在的SKU记录(AI标注): {product.sku_id}, is_handled从{old_is_handled}更新为1")
                else:
                    # 创建新的SKU记录
                    new_sku = models.ItemSKU(
                        sku=product.sku_id,
                        item_name=product.item_name,
                        cleaned_name=product.cleaned_name,
                        item_brand=product.item_brand,
                        item_specs=product.item_specs,
                        item_classification=product.item_classification,
                        item_img=product.item_img,
                        base_unit=result.get("base_unit", "件"),
                        package_type=product.package_type,
                        base_num=product.base_num,  # 添加base_num字段
                        is_handled=1,
                        created_at=datetime.now(),
                        updated_at=datetime.now()
                    )
                    db.add(new_sku)
                    logger.info(f"创建新的SKU记录(AI标注): {product.sku_id}")
        else:
            logger.warning("AI标注服务返回空结果或调用失败，使用默认值处理")
            for product in products_need_ai_label:
                product.package_type = "散装"
                product.cleaned_name = product.item_name
                # 使用默认的 base_num 计算基础价格
                product.base_num = 1.0  # 添加默认的base_num值
                logger.warning(f"商品 {product.item_name} 的价格: {product.item_price}")
                product.base_price = round(product.item_price / product.base_num, 2) if product.item_price is not None else None

                # 检查是否存在SKU记录
                existing_sku = db.query(models.ItemSKU).filter(
                    models.ItemSKU.sku == product.sku_id
                ).first()
                logger.info(f"默认处理后查询SKU记录 {product.sku_id}，找到记录: {existing_sku is not None}，is_handled: {existing_sku.is_handled if existing_sku else 'N/A'}")

                if existing_sku:
                    # 更新已存在的SKU记录
                    old_is_handled = existing_sku.is_handled
                    existing_sku.item_name = product.item_name
                    existing_sku.cleaned_name = product.cleaned_name
                    existing_sku.package_type = product.package_type
                    existing_sku.base_num = product.base_num  # 添加base_num字段
                    existing_sku.is_handled = 1
                    existing_sku.updated_at = datetime.now()
                    logger.info(f"更新已存在的SKU记录(默认值): {product.sku_id}, is_handled从{old_is_handled}更新为1")
                else:
                    # 创建新的SKU记录
                    new_sku = models.ItemSKU(
                        sku=product.sku_id,
                        item_name=product.item_name,
                        cleaned_name=product.cleaned_name,
                        package_type=product.package_type,
                        base_num=product.base_num,  # 添加base_num字段
                        is_handled=1,
                        created_at=datetime.now(),
                        updated_at=datetime.now()
                    )
                    db.add(new_sku)
                    logger.info(f"创建新的SKU记录(默认值): {product.sku_id}, is_handled设置为1")

        # 将AI处理过的商品重新添加到数据库会话和返回列表中
        for product in products_need_ai_label:
            if product not in db_products:
                db.add(product)
                db_products.append(product)

        db.commit()
        logger.info("数据库事务提交成功")
        for product in db_products:
            db.refresh(product)
        logger.info(f"批量创建商品完成，共处理 {len(db_products)} 个商品")
        return db_products
    except Exception as e:
        db.rollback()
        logger.error(f"批量创建商品失败: {e}")
        raise HTTPException(status_code=400, detail=f"批量创建商品失败: {e}")
    
"""
获取所有商品列表
---
参数:
- skip: 跳过的记录数（分页用）
- limit: 返回的最大记录数（分页用）
- db: 数据库会话

返回:
- 200: 返回商品列表和总数
"""
@router.get("/products/")
async def get_products(
    skip: int = 0, #分页起始位置
    limit: int = 100, #分页返回最大记录数
    db: Session = Depends(get_db),
    current_user = Depends(get_current_active_user),
    name: Optional[str] = None, #商品名称
    category_id: Optional[str] = None, #商品分类
    brand_id: Optional[str] = None, #商品品牌
    shop_id: Optional[str] = None, #商品店铺
    shop_store_id: Optional[str] = None,  # 添加shop_store_id参数
    start_date: Optional[str] = None, #商品创建开始时间
    end_date: Optional[str] = None, #商品创建结束时间
):
    # 根据name、category_id、brand_id、shop_id查询商品
    query = db.query(Product)
    if name:
        query = query.filter(Product.item_name.like(f"%{name}%"))
    if category_id:
        query = query.filter(Product.item_classification == category_id)
    if brand_id:
        query = query.filter(Product.item_brand == brand_id)
    if shop_id:
        query = query.filter(Product.shop_id == int(shop_id))
    if shop_store_id:
        query = query.filter(Product.shop_store_id == int(shop_store_id))
    if start_date:
        query = query.filter(Product.created_at >= start_date)
    if end_date:
        query = query.filter(Product.created_at <= end_date)
    # 获取总数
    total = query.count()
    # 获取分页数据
    fields = [Product.sku_id, Product.item_name, Product.item_brand, Product.cleaned_name, Product.item_specs, Product.item_classification, Product.item_img,  Product.item_price, Product.base_unit, Product.base_price   ]
    field_names = ['sku_id', 'item_name', 'item_brand', 'cleaned_name', 'item_specs', 'item_classification', 'item_img', 'item_price', 'base_unit', 'base_price']

    products = query.with_entities(*fields).offset(skip).limit(limit).all()
    result = [dict(zip(field_names, row)) for row in products]
    return {"items": result, "total": total}

"""
获取商品详情
---
参数:
- product_id: 商品ID
- db: 数据库会话

返回:
- 200: 返回商品详细信息
- 404: 商品不存在
"""
@router.get("/products/{product_id}", response_model=ProductResponse)
async def get_product(
    product_id: int,
    db: Session = Depends(get_db)
):
    product = db.query(Product).filter(Product.id == product_id).first()
    if not product:
        raise HTTPException(status_code=404, detail="商品不存在")
    return product

"""
更新商品信息
---
参数:
- product_id: 商品ID
- product: 更新的商品信息
- db: 数据库会话
- current_user: 当前登录用户

返回:
- 200: 更新成功，返回更新后的商品信息
- 404: 商品不存在
- 400: 更新失败，输入数据有误
"""
@router.put("/products/{product_id}", response_model=ProductResponse)
async def update_product(
    product_id: int,
    product: ProductCreate,
    db: Session = Depends(get_db),
    current_user = Depends(get_current_active_user)
):
    db_product = db.query(Product).filter(Product.id == product_id).first()
    if not db_product:
        raise HTTPException(status_code=404, detail="商品不存在")
    
    for key, value in product.dict().items():
        setattr(db_product, key, value)
    
    try:
        db.commit()
        db.refresh(db_product)
        return db_product
    except Exception as e:
        db.rollback()
        raise HTTPException(status_code=400, detail="商品更新失败，请检查输入数据")

"""
删除商品
---
参数:
- product_id: 商品ID
- db: 数据库会话
- current_user: 当前登录用户

返回:
- 200: 删除成功
- 404: 商品不存在
"""
@router.delete("/products/{product_id}")
async def delete_product(
    product_id: int,
    db: Session = Depends(get_db),
    current_user = Depends(get_current_active_user)
):
    product = db.query(Product).filter(Product.id == product_id).first()
    if not product:
        raise HTTPException(status_code=404, detail="商品不存在")
    
    db.delete(product)
    db.commit()
    return {"message": "商品删除成功"}

"""
创建新店铺
---
参数:
- shop: 店铺创建模型，包含名称、描述、地址和联系方式
- db: 数据库会话
- current_user: 当前登录用户

返回:
- 201: 创建成功，返回店铺详情
- 400: 创建失败，输入数据有误
"""
@router.post("/", response_model=ShopResponse, status_code=201)
async def create_shop(
    shop: ShopCreate,
    db: Session = Depends(get_db),
    current_user = Depends(get_current_active_user)
):
    
    # 通过store_id和shop_address查询数据库，判断当前数据是否存在
    existing_shop = db.query(Shop).filter(Shop.shop_name == shop.shop_name, Shop.store_id == shop.store_id, Shop.shop_address == shop.shop_address).first()
    if existing_shop:
        raise HTTPException(status_code=400, detail="店铺已存在")

    db_shop = Shop(**shop.dict())
    db.add(db_shop)
    try:
        db.commit()
        db.refresh(db_shop)
        return db_shop
    except Exception as e:
        db.rollback()
        raise HTTPException(status_code=400, detail="店铺创建失败，请检查输入数据")

"""
获取店铺详情
---
参数:
- id: 店铺ID
- db: 数据库会话

返回:
- 200: 返回店铺详细信息
- 404: 店铺不存在
"""
@router.get("/{id}", response_model=ShopResponse)
async def get_shop(
    id: int,
    db: Session = Depends(get_db)
):
    shop = db.query(Shop).filter(Shop.id == id).first()
    if not shop:
        raise HTTPException(status_code=404, detail="店铺不存在")
    return shop

"""
更新店铺信息
---
参数:
- id: 店铺ID
- shop: 更新的店铺信息
- db: 数据库会话
- current_user: 当前登录用户

返回:
- 200: 更新成功，返回更新后的店铺信息
- 404: 店铺不存在
- 400: 更新失败，输入数据有误
"""
@router.put("/{id}", response_model=ShopResponse)
async def update_shop(
    id: int,
    shop: ShopBase,  # 改用 ShopBase 而不是 ShopCreate，因为更新时字段是可选的
    db: Session = Depends(get_db),
    current_user = Depends(get_current_active_user)
):
    try:
        db_shop = db.query(Shop).filter(Shop.id == id).first()
        if not db_shop:
            raise HTTPException(status_code=404, detail="店铺不存在")
        
        # 只更新提供的字段
        update_data = shop.dict(exclude_unset=True)
        for key, value in update_data.items():
            setattr(db_shop, key, value)
        
        try:
            db.commit()
            db.refresh(db_shop)
            return db_shop
        except Exception as e:
            db.rollback()
            raise HTTPException(status_code=400, detail=f"店铺更新失败: {str(e)}")
    except ValidationError as e:
        raise HTTPException(status_code=422, detail=str(e.errors()))
    

"""
更新店铺信息的状态为启用
---
参数:
- store_id: 店铺store_id
- shop: 更新的店铺信息
- db: 数据库会话
- current_user: 当前登录用户

返回:
- 200: 更新成功，返回更新后的店铺信息
- 404: 店铺不存在
- 400: 更新失败，输入数据有误
"""
@router.put("/update_enable_t/{store_id}", response_model=ShopUpdateBase)
async def update_shop(
    store_id: int,
    db: Session = Depends(get_db),
    current_user = Depends(get_current_active_user)
):
    try:
        db_shop = db.query(Shop).filter(Shop.store_id == store_id).first()
        if not db_shop:
            raise HTTPException(status_code=404, detail="店铺不存在")
        
        # 只更新提供的字段
        shop = ShopUpdateBase(
            enabled = "启用"
        )
        update_data = shop.dict(exclude_unset=True)
        for key, value in update_data.items():
            setattr(db_shop, key, value)
        
        try:
            db.commit()
            db.refresh(db_shop)
            return db_shop
        except Exception as e:
            db.rollback()
            raise HTTPException(status_code=400, detail=f"店铺更新失败: {str(e)}")
    except ValidationError as e:
        raise HTTPException(status_code=422, detail=str(e.errors()))

"""
更新店铺信息的状态为禁用
---
参数:
- store_id: 店铺store_id
- shop: 更新的店铺信息
- db: 数据库会话
- current_user: 当前登录用户

返回:
- 200: 更新成功，返回更新后的店铺信息
- 404: 店铺不存在
- 400: 更新失败，输入数据有误
"""
@router.put("/update_enable_f/{store_id}", response_model=ShopUpdateBase)
async def update_shop(
    store_id: int,
    db: Session = Depends(get_db),
    current_user = Depends(get_current_active_user)
):
    try:
        db_shop = db.query(Shop).filter(Shop.store_id == store_id).first()
        if not db_shop:
            raise HTTPException(status_code=404, detail="店铺不存在")
        
        # 只更新提供的字段
        shop = ShopUpdateBase(
            enabled = "禁用"
        )
        update_data = shop.dict(exclude_unset=True)
        for key, value in update_data.items():
            setattr(db_shop, key, value)
        
        try:
            db.commit()
            db.refresh(db_shop)
            return db_shop
        except Exception as e:
            db.rollback()
            raise HTTPException(status_code=400, detail=f"店铺更新失败: {str(e)}")
    except ValidationError as e:
        raise HTTPException(status_code=422, detail=str(e.errors()))
    

"""
获取店铺的所有商品
---
参数:
- id: 店铺ID
- db: 数据库会话

返回:
- 200: 返回该店铺的所有商品列表
"""
@router.get("/{id}/products", response_model=List[ProductResponse])
async def get_shop_products(
    id: int,
    db: Session = Depends(get_db)
):
    products = db.query(Product).filter(Product.shop_id == id).all()
    return products

"""
获取所有商品分类
---
参数:
- db: 数据库会话

返回:
- 200: 返回所有商品分类列表
"""
@router.get("/categories/", response_model=List[CategoryResponse])
async def get_categories(
    db: Session = Depends(get_db)
):
    categories = db.query(Category).all()
    return [category.to_response() for category in categories]

"""
创建新的商品分类
---
参数:
- category: 分类创建模型，包含名称和描述
- db: 数据库会话
- current_user: 当前登录用户

返回:
- 201: 创建成功，返回分类详情
- 400: 创建失败，输入数据有误
"""
@router.post("/categories/", response_model=CategoryResponse, status_code=201)
async def create_category(
    category: CategoryCreate,
    db: Session = Depends(get_db),
    current_user = Depends(get_current_active_user)
):
    try:
        db_category = Category(
            category_id=category.category_id,
            category_name=category.category_name,
            first_category_id=category.first_category_id,
            first_category_name=category.first_category_name,
            category_type=category.category_type
        )
        db.add(db_category)
        db.commit()
        db.refresh(db_category)
        return db_category.to_response()
    except Exception as e:
        db.rollback()
        raise HTTPException(status_code=400, detail=f"分类创建失败：{str(e)}")

"""
获取所有店铺列表
---
参数:
- skip: 跳过的记录数（分页用）
- limit: 返回的最大记录数（分页用）
- shop_name: 按店铺名称筛选（可选）
- region: 按地区筛选（可选）
- db: 数据库会话

返回:
- 200: 返回店铺列表和总数
"""
@router.get("/")
async def get_shops(
    skip: int = 0,
    limit: int = 100,
    shop_name: Optional[str] = None,
    region: Optional[str] = None,
    enabled: Optional[str] = None,
    check_item_count: Optional[bool] = False,
    db: Session = Depends(get_db),
    current_user = Depends(get_current_active_user)
):
    # 添加 1 秒延迟
    await asyncio.sleep(1)
    
    query = db.query(Shop).with_entities(Shop.store_id, Shop.shop_name, Shop.shop_address, Shop.logo_img, Shop.longitude, Shop.latitude, Shop.provincia_level_name, Shop.municipa_level_name, Shop.district_level_name, Shop.region_name, Shop.enabled, Shop.present_state, Shop.item_count)
    
    # 添加过滤条件
    if shop_name:
        query = query.filter(Shop.shop_name.like(f"%{shop_name}%"))
    if region:
        query = query.filter(
            (Shop.provincia_level_name.like(f"%{region}%")) |
            (Shop.municipa_level_name.like(f"%{region}%")) |
            (Shop.district_level_name.like(f"%{region}%"))
        )
    if enabled:
        query = query.filter(Shop.enabled == enabled)
    
    # 获取总数
    total = query.count()
    
    # 获取分页数据
    shops = query.offset(skip).limit(limit).all()

    # 转为字典列表
    field_names = [
        'store_id', 'shop_name', 'shop_address', 'logo_img',
        'longitude', 'latitude', 'provincia_level_name',
        'municipa_level_name', 'district_level_name', 'region_name',
        'enabled', 'item_count',
    ]
    result = [dict(zip(field_names, row)) for row in shops]

    # 对于每个店铺，获取其商品数量
    for shop in result:
        shop['item_count'] = 0  # 或 None
    if check_item_count:
        for i, shop in enumerate(result):
            shop_id = shop.get('store_id')
            # 这里假设Product.shop_id和Shop.store_id关联
            shop['item_count'] = db.query(Product).filter(Product.shop_id == shop_id).count()

    return {
        "items": result,
        "total": total
    }

"""
通过地址查询超市
---
参数:
- address: 地址字符串
- db: 数据库会话

返回:
- 200: 返回匹配的超市列表
"""
@router.get("/shops/by_address/")
async def get_shops_by_address(
    address: str,
    db: Session = Depends(get_db)
):
    from backend.models.shop import BaseConfiguration

    run_id = str(uuid.uuid4())
    clean_parameters = {"查询地址": f"{address}"}
    execution = WingknifeExecutionRecord(
        process_id=100,
        run_id=run_id,
        parameters=clean_parameters,
        status="running",
        created_by="system"
    )
    db.add(execution)
    db.commit()
    db.refresh(execution)

    config = db.query(BaseConfiguration).first()
    if not config:
        execution.status = "failed"
        execution.result = {"error": "基础配置信息未找到"}
        execution.completed_at = datetime.now()
        db.commit()
        raise HTTPException(status_code=404, detail="基础配置信息未找到")

    # 地址接口模板
    address_api_url_template = "https://trading.dmall.com/amap/restapi?param=%7B%22terminal%22%3A%22devtools%22%2C%22platform%22%3A%229%22%2C%22channel%22%3A%22miniprograms%22%2C%22loginId%22%3A%22ec5e2a71-8c58-4dc1-8a3b-5b5f711235fa%22%2C%22deviceId%22%3A%22CB20611BED900002A7F415D7160078701742101995265%22%2C%22v%22%3A%22v5.6.4%22%2C%22appVersion%22%3A%225.6.4%22%2C%22requestVersion%22%3A%225.6.4%22%2C%22hideLoader%22%3Atrue%2C%22path%22%3A%22%2Fv3%2Fplace%2Ftext%22%2C%22parameters%22%3A%22extensions%3Dall%26radius%3D200%26types%3D%25E6%25A5%25BC%25E6%25A0%258B%25E5%258F%25B7%257C%25E8%25B4%25AD%25E7%2589%25A9%25E6%259C%258D%25E5%258A%25A1%257C%25E4%25B9%25A1%25E9%2595%2587%25E7%25BA%25A7%25E5%259C%25B0%25E5%2590%258D%257C%25E9%25A4%2590%25E9%25A5%25AE%25E6%259C%258D%25E5%258A%25A1%257C%25E5%2585%25AC%25E5%258F%25B8%25E4%25BC%2581%25E4%25B8%259A%257C%25E7%25A7%2591%25E6%2595%2599%25E6%2596%2587%25E5%258C%2596%25E6%259C%258D%25E5%258A%25A1%257C%25E8%25B7%25AF%25E5%258F%25A3%25E5%2590%258D%257C%25E7%2583%2598%25E7%2582%25B9%25E5%259C%25B0%25E5%2590%258D%257C%25E5%258C%25BB%25E7%2596%2597%25E4%25BF%259D%25E5%2581%25A5%25E6%259C%258D%25E5%258A%25A1%257C%25E7%2594%259F%25E6%25B4%25BB%25E6%259C%258D%25E5%258A%25A1%257C%25E9%2593%25B6%25E8%25A1%258C%257C%25E4%25BF%259D%25E9%2599%25A9%25E5%2585%25AC%25E5%258F%25B8%257C%25E5%2585%25AC%25E5%258F%25B8%257C%25E7%25BB%25BC%25E5%2590%2588%25E5%258C%25BB%25E9%2599%25A2%257C%25E4%25BD%258F%25E5%25AE%25BF%25E6%259C%258D%25E5%258A%25A1%257C%25E6%2594%25BF%25E5%25BA%259C%25E6%259C%25BA%25E6%259E%2584%25E5%258F%258A%25E7%25A4%25BE%25E4%25BC%259A%25E5%259B%25A2%25E4%25BD%2593%257C%25E8%25BF%2590%25E5%258A%25A8%25E5%259C%25BA%25E9%25A6%2586%257C%25E5%25AD%25A6%25E6%25A0%25A1%257C%25E5%2595%2586%25E5%258A%25A1%25E4%25BD%258F%25E5%25AE%2585%257C%25E9%2597%25A8%25E7%2589%258C%25E4%25BF%25A1%25E6%2581%25AF%257C%25E6%259D%2591%25E7%25BB%2584%25E7%25BA%25A7%25E5%259C%25B0%25E5%2590%258D%257C%25E6%259D%2591%25E5%25BA%2584%25E7%25BA%25A7%25E5%259C%25B0%25E5%2590%258D%257C%25E6%25A0%2587%25E5%25BF%2597%25E6%2580%25A7%25E5%25BB%25BA%25E7%25AD%2591%25E7%2589%25A9%26keywords%3D%25E6%25B2%2599%25E6%25B2%25B3%25E5%259C%25B0%25E9%2593%2581%25E7%25AB%2599%26city%3D%E5%8C%97%E4%BA%AC%E5%B8%82%22%7D&d_track_data=%7B%22session_id%22%3A%22CB2E3E1E8DB00002CCF36C1034D035A01745823402243%22%2C%22project%22%3A%22%E4%B8%BB%E5%B0%8F%E7%A8%8B%E5%BA%8F%22%2C%22tdc%22%3A%22%22%2C%22tpc%22%3A%22%22%2C%22uuid%22%3A%22CB20611BED900002A7F415D7160078701742101995265%22%2C%22env%22%3A%22minip%22%7D"

    # 1. 对地址进行URL编码并替换%
    encoded_address = quote(address).replace('%', '%25')

    # 2. 直接字符串替换参数
    url = address_api_url_template
    url = url.replace('session_id%22%3A%22CB2E3E1E8DB00002CCF36C1034D035A01745823402243', f'session_id%22%3A%22{config.session_id}')
    url = url.replace('loginId%22%3A%22ec5e2a71-8c58-4dc1-8a3b-5b5f711235fa', f'loginId%22%3A%22{config.login_id}')
    url = url.replace('deviceId%22%3A%22CB20611BED900002A7F415D7160078701742101995265', f'deviceId%22%3A%22{config.device_id}')
    url = url.replace('v%22%3A%22v5.6.4', f'v%22%3A%22{config.v}')
    url = url.replace('appVersion%22%3A%225.6.4', f'appVersion%22%3A%22{config.app_version}')
    url = url.replace('requestVersion%22%3A%225.6.4', f'requestVersion%22%3A%22{config.app_version}')
    url = url.replace('uuid%22%3A%22CB20611BED900002A7F415D7160078701742101995265', f'uuid%22%3A%22{config.uuid}')
    # keywords参数替换
    import re
    url = re.sub(r'keywords%3D[^%]*', f'keywords%3D{encoded_address}', url)

    # 3. 发送GET请求
    try:
        async with httpx.AsyncClient(timeout=10.0) as client:
            resp = await client.get(url)
            resp.raise_for_status()
            resp_json = resp.json()
    except Exception as e:
        execution.status = "failed"
        execution.result = {"error": f"多点查询地址接口请求失败: {str(e)}"}
        execution.completed_at = datetime.now()
        db.commit()
        raise HTTPException(status_code=500, detail=f"地址接口请求失败: {str(e)}")

    # 4. 解析返回信息，提取坐标
    pois = resp_json.get('data', {}).get('pois', [])
    if not pois or not pois[0].get('location'):
        execution.status = "failed"
        execution.result = {"error": f"多点处理地址接口返回信息失败: {str(e)}，返回信息：{resp_json}"}
        execution.completed_at = datetime.now()
        db.commit()
        return {"generated_url": url, "message": "未获取到POI位置信息"}
    location = pois[0]['location']
    coords = location.split(',')
    x = coords[0] if len(coords) > 0 else None
    y = coords[1] if len(coords) > 1 else None

    # 5. 构造超市接口URL
    supermarket_api_url_template = (
        'https://flow.dmall.com/address/business?param={"terminal":"devtools","platform":"9","channel":"miniprograms","loginId":"ec5e2a71-8c58-4dc1-8a3b-5b5f711235fa","deviceId":"CB20611BED900002A7F415D7160078701742101995265","v":"v5.6.4","appVersion":"5.6.4","requestVersion":"5.6.4","hideLoading":true,"resType":1,"designatedStoreId":"","venderId":"","thirdStoreId":"","scene":"o2o","latitude":"39.973475","longitude":"116.358382"}'
        '&d_track_data={"session_id":"CB2E3E1E8DB00002CCF36C1034D035A01745823402243","project":"主小程序","tdc":"","tpc":"","uuid":"CB20611BED900002A7F415D7160078701742101995265","env":"minip"}'
    )
    supermarket_url = supermarket_api_url_template
    supermarket_url = supermarket_url.replace('loginId":"ec5e2a71-8c58-4dc1-8a3b-5b5f711235fa', f'loginId":"{config.login_id}')
    supermarket_url = supermarket_url.replace('deviceId":"CB20611BED900002A7F415D7160078701742101995265', f'deviceId":"{config.device_id}')
    supermarket_url = supermarket_url.replace('v":"v5.6.4', f'v":"{config.v}')
    supermarket_url = supermarket_url.replace('appVersion":"5.6.4', f'appVersion":"{config.app_version}')
    supermarket_url = supermarket_url.replace('requestVersion":"5.6.4', f'requestVersion":"{config.app_version}')
    supermarket_url = supermarket_url.replace('latitude":"39.973475', f'latitude":"{y}')
    supermarket_url = supermarket_url.replace('longitude":"116.358382', f'longitude":"{x}')
    supermarket_url = supermarket_url.replace('session_id":"CB2E3E1E8DB00002CCF36C1034D035A01745823402243', f'session_id":"{config.session_id}')
    supermarket_url = supermarket_url.replace('uuid":"CB20611BED900002A7F415D7160078701742101995265', f'uuid":"{config.uuid}')

    # 6. 请求超市接口
    try:
        async with httpx.AsyncClient(timeout=10.0) as client:
            resp2 = await client.get(supermarket_url)
            resp2.raise_for_status()
            resp2_json = resp2.json()
    except Exception as e:
        execution.status = "failed"
        execution.result = {"error": f"多点查询地址附近超市接口请求失败: {str(e)}"}
        execution.completed_at = datetime.now()
        db.commit()
        raise HTTPException(status_code=500, detail=f"超市接口请求失败: {str(e)}")

    # 7. 处理返回信息
    store_list = resp2_json.get('data', {}).get('storeInfoVOList', [])
    result = []
    saved_stores = []  # 用于存储保存成功的店铺信息

    for store in store_list:
        if store.get('venderName') == '北京物美' and store.get('storeName') != '精选云超':
            store_info = {
                'storeName': store.get('storeName'),
                'erpStoreId': store.get('erpStoreId'),
                'venderLogo': store.get('venderLogo'),
                'address': store.get('address'),
                'longitude': store.get('longitude'),
                'latitude': store.get('latitude'),
                'provincialLevelName': store.get('provincialLevelName'),
                'municipalLevelName': store.get('municipalLevelName'),
                'districtLevelName': store.get('districtLevelName'),
                'regionName': store.get('regionName'),
            }
            result.append({
                'storeName': store.get('storeName'),
                'erpStoreId': store.get('erpStoreId'),
                'address': store.get('address'),
                'shipping_address': address,
            })

            # 检查店铺是否已存在
            existing_shop = db.query(Shop).filter(
                Shop.shop_name == store_info['storeName'],
                Shop.store_id == store_info['erpStoreId'],
                Shop.shop_address == store_info['address']
            ).first()

            if not existing_shop:
                # 创建新店铺
                new_shop = Shop(
                    shop_name=store_info['storeName'],
                    store_id=store_info['erpStoreId'],
                    logo_img=store_info['venderLogo'],
                    shop_address=store_info['address'],
                    longitude=store_info['longitude'],
                    latitude=store_info['latitude'],
                    provincia_level_name=store_info['provincialLevelName'],
                    municipa_level_name=store_info['municipalLevelName'],
                    district_level_name=store_info['districtLevelName'],
                    region_name=store_info['regionName'],
                    enabled="启用",  # 设置默认值
                    item_count=0,    # 设置默认值
                    present_state="未执行",  # 设置默认值
                    shipping_address=address,  # 设置为查询的地址
                    created_at=datetime.utcnow()
                )
                try:
                    db.add(new_shop)
                    db.commit()
                    db.refresh(new_shop)
                    saved_stores.append(new_shop)
                except Exception as e:
                    db.rollback()
                    execution.status = "failed"
                    execution.result = {"error": f"保存店铺失败: {str(e)}"}
                    execution.completed_at = datetime.now()
                    db.commit()
                    logger.error(f"保存店铺失败: {str(e)}")
                    continue

    execution.status = "completed"
    execution.result = {"附近店铺": f"{result}"}
    execution.completed_at = datetime.now()
    db.commit()
    return {
        "code": 200,
        "message": "查询商品地址完成",
        # 'supermarket_url': supermarket_url,
        'filtered_stores': result,
        # 'saved_stores': [
        #     {
        #         'id': store.id,
        #         'shop_name': store.shop_name,
        #         'store_id': store.store_id,
        #         'address': store.shop_address
        #     } for store in saved_stores
        # ]
    }

"""
通过超市store_id查询商品
---
参数:
- shop_id: 超市ID
- db: 数据库会话

返回:
- 200: 返回该超市的商品列表
"""
@router.get("/shops/{shop_id}/products_by_shop/")
async def get_products_by_shop(shop_id: int, db: Session = Depends(get_db)):
    # 生成运行记录
    run_id = str(uuid.uuid4())
    clean_parameters = {"待查寻超市store_id": f"{shop_id}"}
    execution = WingknifeExecutionRecord(
        process_id=101,
        run_id=run_id,
        parameters=clean_parameters,
        status="running",
        created_by="system"
    )
    db.add(execution)
    db.commit()
    db.refresh(execution)

    """根据超市ID（store_id）查询商品，前置校验逻辑"""
    # 1. 查找店铺
    shop = db.query(Shop).filter(Shop.store_id == shop_id).first()
    if not shop:
        execution.status = "failed"
        execution.result = {"error": "未找到对应的超市信息"}
        execution.completed_at = datetime.now()
        db.commit()
        raise HTTPException(status_code=404, detail="未找到对应的超市信息")
    # 2. 检查enabled字段
    if shop.enabled != "启用":
        execution.status = "failed"
        execution.result = {"error": "该超市未启用，无法查询商品"}
        execution.completed_at = datetime.now()
        db.commit()
        raise HTTPException(status_code=400, detail="该超市未启用，无法查询商品")
    # 3. 获取BaseConfiguration配置信息
    from backend.models.shop import BaseConfiguration
    config = db.query(BaseConfiguration).first()
    if not config:
        execution.status = "failed"
        execution.result = {"error": "基础配置信息未找到"}
        execution.completed_at = datetime.now()
        db.commit()
        raise HTTPException(status_code=404, detail="基础配置信息未找到")
    
    # 4. 声明商品接口地址变量
    product_api_url_template = 'https://searchgw.dmall.com/app/new/search/wareSearch/v1?param={"terminal":"devtools","platform":"9","channel":"miniprograms","loginId":"","deviceId":"","v":"","appVersion":"","requestVersion":"","src":0,"pageSize":20,"queryType":0,"isOffline":false,"categoryLevel":2,"fromType":1,"noResultSearch":0,"businessCode":0,"pos":0,"pageNum":1,"from":1,"storeInfo":{"venderId":1,"defaultChosed":false,"storeId":,"timestamp":"","name":"全部业态","businessCode":"99"},"categoryType":1,"requestSource":"9","categoryId":"","firstCategoryId":"","secondCategoryId":"","freeVersion":""}&d_track_data={"session_id":"","project":"主小程序","tdc":"","tpc":"","uuid":"","env":"minip"}'
    
    # 5. 替换商品接口相关信息
    product_api_url = product_api_url_template
    product_api_url = product_api_url.replace('"loginId":""', f'"loginId":"{config.login_id}"')
    product_api_url = product_api_url.replace('"deviceId":""', f'"deviceId":"{config.device_id}"')
    product_api_url = product_api_url.replace('"v":""', f'"v":"{config.v}"')
    product_api_url = product_api_url.replace('"requestVersion":""', f'"requestVersion":"{config.v}"')
    product_api_url = product_api_url.replace('"appVersion":""', f'"appVersion":"{config.request_version}"')
    product_api_url = product_api_url.replace('"freeVersion":""', f'"freeVersion":"{config.free_version}"')
    product_api_url = product_api_url.replace('"uuid":""', f'"uuid":"{config.uuid}"')
    product_api_url = product_api_url.replace('"session_id":""', f'"session_id":"{config.session_id}"')
    product_api_url = product_api_url.replace('"storeId":,', f'"storeId":{shop_id},')
    
    # 6. 声明分类接口地址变量
    category_api_url_template = 'https://searchgw.dmall.com/app/new/wareCategory/list/v3?param={"terminal":"devtools","platform":"9","channel":"miniprograms","loginId":"","deviceId":"","v":"","appVersion":"","requestVersion":"","storeInfo":{"venderId":1,"defaultChosed":false,"storeId":,"timestamp":"","name":"","businessCode":99},"requestSource":"9","queryType":0,"from":1}&d_track_data={"session_id":"","project":"主小程序","tdc":"","tpc":"","uuid":"","env":"minip"}'
    
    # 7. 替换分类接口相关信息
    category_api_url = category_api_url_template
    category_api_url = category_api_url.replace('"loginId":""', f'"loginId":"{config.login_id}"')
    category_api_url = category_api_url.replace('"deviceId":""', f'"deviceId":"{config.device_id}"')
    category_api_url = category_api_url.replace('"v":""', f'"v":"{config.v}"')
    category_api_url = category_api_url.replace('"requestVersion":""', f'"requestVersion":"{config.v}"')
    category_api_url = category_api_url.replace('"appVersion":""', f'"appVersion":"{config.request_version}"')
    category_api_url = category_api_url.replace('"uuid":""', f'"uuid":"{config.uuid}"')
    category_api_url = category_api_url.replace('"session_id":""', f'"session_id":"{config.session_id}"')
    category_api_url = category_api_url.replace('"storeId":,', f'"storeId":{shop_id},')
    
    # 8. 使用POST请求请求分类接口
    try:
        async with httpx.AsyncClient(timeout=10.0) as client:
            category_response = await client.post(category_api_url)
            category_response.raise_for_status()
            category_data = category_response.json()
    except Exception as e:
        execution.status = "failed"
        execution.result = {"error": f"分类接口请求失败: {str(e)}"}
        execution.completed_at = datetime.now()
        db.commit()
        raise HTTPException(status_code=500, detail=f"分类接口请求失败: {str(e)}")
    
    # 新增：声明待查询类型列表
    # query_category_names = [
    #     "蔬菜豆制品","肉蛋水产","水果鲜花","粮油调味","速食冻品","酒水饮料"
    # ]
    query_category_names = [
        "蔬菜豆制品","肉蛋水产","水果鲜花","粮油调味","速食冻品","酒水饮料"
    ]
    
    # 9. 数据处理一：获取分类列表
    ware_category = category_data.get('data', {}).get('wareCategory', [])
    if not ware_category:
        execution.status = "failed"
        execution.result = {"error": "未获取到分类信息"}
        execution.completed_at = datetime.now()
        db.commit()
        raise HTTPException(status_code=404, detail="未获取到分类信息")
    
    category_list = ware_category[0].get('categoryList', [])
    if not category_list:
        execution.status = "failed"
        execution.result = {"error": "未获取到分类列表"}
        execution.completed_at = datetime.now()
        db.commit()
        raise HTTPException(status_code=404, detail="未获取到分类列表")
    
    # 10. 声明商品信息列表
    products_to_create = []
    #成功数量
    success_num = 0
    #失败数量
    fail_num = 0
    # 大循环：遍历大分类
    for main_category in category_list:
        main_category_name = main_category.get('categoryName', '')
        main_category_id = main_category.get('categoryId', '')
        category_type = main_category.get('categoryType', 1)
        if main_category_name not in query_category_names:
            continue
        # 小循环：遍历子分类
        for child_category in main_category.get('childCategoryList', []):
            # 声明循环内状态是否正常
            status_ok = True
            # 声明页码参数
            page_num = 1
            page_count = 2
            child_category_id = child_category.get('categoryId', '')
            # 商品接口URL替换
            current_product_api_url = product_api_url
            current_product_api_url = current_product_api_url.replace('"categoryId":""', f'"categoryId":"{child_category_id}"')
            current_product_api_url = current_product_api_url.replace('"secondCategoryId":""', f'"secondCategoryId":"{child_category_id}"')
            current_product_api_url = current_product_api_url.replace('"firstCategoryId":""', f'"firstCategoryId":"{main_category_id}"')
            current_product_api_url = current_product_api_url.replace('"categoryType":1', f'"categoryType":{category_type}')
            current_product_api_url = current_product_api_url.replace('"pageNum":1', f'"pageNum":{page_num}')
            timestamp = int(time.time() * 1000)
            current_product_api_url = current_product_api_url.replace('"timestamp":""', f'"timestamp":"{shop_id}_{timestamp}"')

            while page_num <= page_count:
                try:
                    async with httpx.AsyncClient(timeout=10.0) as client:
                        product_response = await client.get(current_product_api_url)
                        product_response.raise_for_status()
                        product_data = product_response.json()
                except Exception as e:
                    status_ok = False
                    break  # 使用break而不是return，避免后续代码访问未初始化的变量
                
                # 确保product_data不为None
                if not product_data:
                    status_ok = False
                    break
                    
                data = product_data.get('data', {})
                ware_list = data.get('wareList', [])
                for ware in ware_list:
                    item_name = ware.get('wareName', "")
                    # 如果item_name为空，则跳过本条数据
                    if not item_name:
                        continue
                    price_raw = ware.get('warePrice', 0)
                    item_price = round(float(price_raw) / 100, 2) if price_raw else 0.0
                    sku = str(ware.get('sku', ""))
                    item_img = ware.get('wareImg', "")
                    product_dict = {
                        "item_name": item_name,
                        "item_price": item_price,
                        "item_classification": main_category_name,
                        "shop_id": shop.id,
                        "shop_name": shop.shop_name,
                        "sku_id": sku,
                        "item_img": item_img,
                        "run_id": run_id,
                        "shop_store_id": shop_id
                    }
                    products_to_create.append(product_dict)
                if products_to_create:
                    # 增加重试机制
                    max_retries = 3
                    retry_count = 0
                    success = False
                    
                    while retry_count < max_retries and not success:
                        try:
                            # 在异步线程中创建新的数据库会话
                            from backend.database.database import SessionLocal
                            def create_products_from_dict(products_data):
                                """从字典数据创建商品，避免SQLAlchemy会话问题"""
                                db_session = SessionLocal()
                                try:
                                    created_count = 0
                                    # 确保配置管理器已初始化
                                    if not hasattr(config_manager, '_is_initialized') or not config_manager._is_initialized:
                                        config_manager.initialize(db_session)
                                    
                                    # 获取营销词和品牌配置
                                    marketing_words = config_manager.marketing_words
                                    brands = config_manager.brands
                                    
                                    # 只生成一次，放循环外
                                    today_start = datetime.now(timezone.utc).replace(hour=0, minute=0, second=0, microsecond=0)
                                    today_end = today_start + timedelta(days=1) - timedelta(microseconds=1)
                                    # 收集需要AI标注的商品（无已处理SKU或存在未处理SKU）
                                    products_need_ai_label: List[Product] = []
                                    
                                    for product_dict in products_data:
                                        try:
                                            # 创建商品实例
                                            new_item = Product(**product_dict)
                                            
                                            # 查询是否已存在今日相同SKU和店铺的商品
                                            existing_product = db_session.query(models.Item).filter(
                                                models.Item.sku_id == new_item.sku_id,
                                                models.Item.shop_store_id == new_item.shop_store_id,
                                                models.Item.updated_at >= today_start,
                                                models.Item.updated_at <= today_end
                                            ).first()
                                            
                                            if existing_product:
                                                # 如果已存在，跳过
                                                db_session.expunge(new_item)
                                                continue
                                            
                                            # 只有不存在时才添加到数据库
                                            db_session.add(new_item)
                                            
                                            # 根据 sku_id 同步/判断 item_sku 信息
                                            if new_item.sku_id:
                                                matching_sku = db_session.query(models.ItemSKU).filter(
                                                    models.ItemSKU.sku == new_item.sku_id,
                                                    models.ItemSKU.is_handled == 1,
                                                ).first()

                                                if matching_sku:
                                                    new_item.cleaned_name = matching_sku.cleaned_name
                                                    new_item.item_brand = matching_sku.item_brand
                                                    new_item.item_specs = matching_sku.item_specs
                                                    new_item.base_unit = matching_sku.base_unit
                                                    new_item.package_type = matching_sku.package_type
                                                    new_item.base_num = matching_sku.base_num

                                                    # 计算基础价格（已处理SKU）
                                                    if new_item.item_price is not None and new_item.base_unit:
                                                        try:
                                                            conversion_rule = db_session.query(UnitConversion).filter(
                                                                UnitConversion.source_unit == new_item.base_unit,
                                                                UnitConversion.is_active == True
                                                            ).first()

                                                            base_num = matching_sku.base_num if matching_sku.base_num is not None else 1
                                                            if type(base_num) == str:
                                                                base_num = int(base_num)
                                                            if base_num <= 0:
                                                                base_num = 1

                                                            if conversion_rule:
                                                                formula = conversion_rule.conversion_formula
                                                                try:
                                                                    formula_with_quantity = formula.replace('x', str(base_num))
                                                                    calculated_base_price = round(new_item.item_price / eval(formula_with_quantity), 2)
                                                                except (ValueError, TypeError, SyntaxError):
                                                                    calculated_base_price = round(new_item.item_price / base_num, 2)
                                                                new_item.base_price = calculated_base_price
                                                            else:
                                                                try:
                                                                    item_price = float(new_item.item_price)
                                                                    base_num_f = float(base_num) if base_num else 1.0
                                                                    if base_num_f <= 0:
                                                                        base_num_f = 1.0
                                                                    new_item.base_price = round(item_price / base_num_f, 2)
                                                                except (ValueError, TypeError, ZeroDivisionError):
                                                                    new_item.base_price = None
                                                        except Exception:
                                                            new_item.base_price = None
                                                else:
                                                    # 未找到已处理SKU，检查是否存在未处理SKU或完全不存在SKU记录
                                                    unhandled_sku = db_session.query(models.ItemSKU).filter(
                                                        models.ItemSKU.sku == new_item.sku_id,
                                                        models.ItemSKU.is_handled == 0,
                                                    ).first()
                                                    if unhandled_sku is not None:
                                                        products_need_ai_label.append(new_item)
                                                    else:
                                                        # 完全无SKU记录，同样进入AI处理
                                                        products_need_ai_label.append(new_item)
                                            
                                            created_count += 1
                                        except Exception as e:
                                            logger.error(f"创建单个商品失败: {e}, 数据: {product_dict}")
                                            continue
                                    
                                    # 批量AI标注处理（在循环结束后统一执行）
                                    if len(products_need_ai_label) > 0:
                                        item_names = [p.item_name for p in products_need_ai_label]
                                        try:
                                            # 分类依据：以首个商品分类决定分支（与上层一致）
                                            first_cls = products_need_ai_label[0].item_classification
                                            if first_cls in ("酒水饮料", "粮油调味", "速食冻品"):
                                                ai_results = ai_label_sku_lyjs(item_names, config_manager)
                                            else:
                                                ai_results = ai_label_sku(item_names, first_cls, config_manager)
                                        except Exception:
                                            ai_results = None

                                        if ai_results and len(ai_results) > 0:
                                            for product_obj, result in zip(products_need_ai_label, ai_results):
                                                product_obj.package_type = result.get("package_type", "件")
                                                product_obj.item_brand = result.get("brand", "")
                                                product_obj.item_specs = result.get("specs", "")
                                                product_obj.base_unit = result.get("base_unit", "件")
                                                product_obj.cleaned_name = result.get("cleaned_name", product_obj.item_name)
                                                product_obj.base_num = result.get("base_num", 1.0)

                                                # 计算基础价格（AI处理后）
                                                calculated_base_price = None
                                                if product_obj.item_price is not None and product_obj.base_unit:
                                                    try:
                                                        conversion_rule = db_session.query(UnitConversion).filter(
                                                            UnitConversion.source_unit == product_obj.base_unit,
                                                            UnitConversion.is_active == True
                                                        ).first()

                                                        base_num = product_obj.base_num if product_obj.base_num is not None else 1
                                                        if type(base_num) == str:
                                                            base_num = int(base_num)
                                                        if base_num <= 0:
                                                            base_num = 1

                                                        if conversion_rule:
                                                            formula = conversion_rule.conversion_formula
                                                            try:
                                                                formula_with_quantity = formula.replace('x', str(base_num))
                                                                calculated_base_price = round(product_obj.item_price / eval(formula_with_quantity), 2)
                                                                product_obj.base_unit = conversion_rule.target_unit
                                                            except (ValueError, TypeError, SyntaxError):
                                                                calculated_base_price = round(product_obj.item_price / base_num, 2)
                                                        else:
                                                            try:
                                                                item_price = float(product_obj.item_price)
                                                                base_num_f = float(base_num) if base_num else 1.0
                                                                if base_num_f <= 0:
                                                                    base_num_f = 1.0
                                                                calculated_base_price = round(item_price / base_num_f, 2)
                                                            except (ValueError, TypeError, ZeroDivisionError):
                                                                calculated_base_price = None
                                                    except Exception:
                                                        calculated_base_price = None
                                                product_obj.base_price = calculated_base_price

                                                # 更新或创建SKU记录（置为已处理）
                                                existing_sku = db_session.query(models.ItemSKU).filter(
                                                    models.ItemSKU.sku == product_obj.sku_id
                                                ).first()
                                                if existing_sku:
                                                    existing_sku.item_name = product_obj.item_name
                                                    existing_sku.cleaned_name = product_obj.cleaned_name
                                                    existing_sku.item_brand = product_obj.item_brand
                                                    existing_sku.item_specs = product_obj.item_specs
                                                    existing_sku.item_classification = product_obj.item_classification
                                                    existing_sku.item_img = product_obj.item_img
                                                    existing_sku.base_unit = result.get("base_unit", "件")
                                                    existing_sku.package_type = product_obj.package_type
                                                    existing_sku.base_num = product_obj.base_num
                                                    existing_sku.is_handled = 1
                                                    existing_sku.updated_at = datetime.now()
                                                else:
                                                    new_sku = models.ItemSKU(
                                                        sku=product_obj.sku_id,
                                                        item_name=product_obj.item_name,
                                                        cleaned_name=product_obj.cleaned_name,
                                                        item_brand=product_obj.item_brand,
                                                        item_specs=product_obj.item_specs,
                                                        item_classification=product_obj.item_classification,
                                                        item_img=product_obj.item_img,
                                                        base_unit=result.get("base_unit", "件"),
                                                        package_type=product_obj.package_type,
                                                        base_num=product_obj.base_num,
                                                        is_handled=1,
                                                        created_at=datetime.now(),
                                                        updated_at=datetime.now()
                                                    )
                                                    db_session.add(new_sku)
                                        else:
                                            # AI失败时的默认处理：至少保证商品可入库
                                            for product_obj in products_need_ai_label:
                                                product_obj.package_type = product_obj.package_type or "散装"
                                                product_obj.cleaned_name = product_obj.cleaned_name or product_obj.item_name
                                                if product_obj.item_price is not None:
                                                    try:
                                                        base_num = product_obj.base_num if product_obj.base_num is not None else 1.0
                                                        base_num_f = float(base_num) if base_num else 1.0
                                                        if base_num_f <= 0:
                                                            base_num_f = 1.0
                                                        product_obj.base_price = round(float(product_obj.item_price) / base_num_f, 2)
                                                    except (ValueError, TypeError, ZeroDivisionError):
                                                        product_obj.base_price = None

                                                existing_sku = db_session.query(models.ItemSKU).filter(
                                                    models.ItemSKU.sku == product_obj.sku_id
                                                ).first()
                                                if existing_sku:
                                                    existing_sku.item_name = product_obj.item_name
                                                    existing_sku.cleaned_name = product_obj.cleaned_name
                                                    existing_sku.package_type = product_obj.package_type
                                                    existing_sku.base_num = product_obj.base_num or 1.0
                                                    existing_sku.is_handled = 1
                                                    existing_sku.updated_at = datetime.now()
                                                else:
                                                    new_sku = models.ItemSKU(
                                                        sku=product_obj.sku_id,
                                                        item_name=product_obj.item_name,
                                                        cleaned_name=product_obj.cleaned_name,
                                                        package_type=product_obj.package_type or "散装",
                                                        base_num=product_obj.base_num or 1.0,
                                                        is_handled=1,
                                                        created_at=datetime.now(),
                                                        updated_at=datetime.now()
                                                    )
                                                    db_session.add(new_sku)

                                    # 批量提交
                                    db_session.commit()
                                    return created_count
                                except Exception as e:
                                    db_session.rollback()
                                    logger.error(f"批量提交失败: {e}")
                                    raise
                                finally:
                                    db_session.close()
                            
                            created_count = await asyncio.to_thread(create_products_from_dict, products_to_create)
                            success_num += created_count
                            fail_num += len(products_to_create) - created_count
                            success = True
                            logger.info(f"批量创建商品成功，成功数量: {created_count}, 失败数量: {len(products_to_create) - created_count}")
                        except Exception as e:
                            retry_count += 1
                            logger.error(f"批量创建商品失败 (尝试 {retry_count}/{max_retries}): {e}")
                            logger.error(f"错误类型: {type(e).__name__}")
                            logger.error(f"错误详情: {str(e)}")
                            
                            if retry_count < max_retries:
                                # 等待一段时间后重试
                                await asyncio.sleep(2 ** retry_count)  # 指数退避
                                logger.info(f"准备重试批量创建商品...")
                            else:
                                fail_num += len(products_to_create)
                                logger.error(f"批量创建商品最终失败，已重试 {max_retries} 次")
                                print(f"批量创建商品失败: {e}")
                                print(f"错误类型: {type(e).__name__}")
                                print(f"错误详情: {str(e)}")
                current_product_api_url = current_product_api_url.replace(f'"pageNum":{page_num}', '"pageNum":1')
                page_info = data.get('pageInfo', {})
                page_count = page_info.get('pageCount', 1)
                products_to_create = []
                if page_num >= page_count:
                    # 结束循环
                    break
                page_num += 1
                current_product_api_url = current_product_api_url.replace('"pageNum":1', f'"pageNum":{page_num}')
            if not status_ok:
                continue
        
    
    # 优化最终提交逻辑，增加重试机制
    max_commit_retries = 3
    commit_retry_count = 0
    commit_success = False
    
    while commit_retry_count < max_commit_retries and not commit_success:
        try:
            execution.status = "completed"
            execution.result = {f"执行结果":"入库成功数量{success_num}，入库失败数量{fail_num}"}
            execution.completed_at = datetime.now()
            db.commit()
            commit_success = True
            logger.info("执行记录更新成功")
        except Exception as e:
            commit_retry_count += 1
            logger.error(f"更新执行记录失败 (尝试 {commit_retry_count}/{max_commit_retries}): {e}")
            
            if commit_retry_count < max_commit_retries:
                try:
                    db.rollback()
                    await asyncio.sleep(1)  # 短暂等待后重试
                except:
                    pass
            else:
                logger.error("更新执行记录最终失败")
                # 即使更新执行记录失败，也要返回处理结果
                return {"code": 200, "message": f"处理完成，但更新执行记录失败。入库成功数量{success_num}，入库失败数量{fail_num}"}

    return {"code": 200, "message": "处理完成"}

"""
修改基本配置信息
---
参数:
- BaseConfigurationCreate: 基本配置信息
- db: 数据库会话

返回:
- 200: 返回配置更新结果
"""
@router.post("/base_configuration/", response_model=BaseConfigurationResponse)
async def update_base_configuration(
    configuration: BaseConfigurationCreate,
    db: Session = Depends(get_db)
):
     # 生成运行ID
    run_id = str(uuid.uuid4())
    # 记录运行参数
    clean_parameters = {"配置信息": configuration.dict()}
    # 创建执行记录
    execution = WingknifeExecutionRecord(
        process_id=102,
        run_id=run_id,
        parameters=clean_parameters,
        status="running",
        created_by="robot"
    )
    db.add(execution)
    db.commit()
    db.refresh(execution)

    try:
        from backend.models.shop import BaseConfiguration
        config = db.query(BaseConfiguration).first()
        if not config:
            # 新增基本配置信息
            config = BaseConfiguration(
                login_id=configuration.login_id,
                device_id=configuration.device_id,
                v=configuration.v,
                app_version=configuration.app_version,
                request_version=configuration.request_version,
                free_version=configuration.free_version,
                session_id=configuration.session_id,
                uuid=configuration.uuid
            )
            db.add(config)
            db.commit()
            db.refresh(config)
            logger.info("新增基本配置信息")
        else:
            # 更新基本配置信息
            config.login_id = configuration.login_id if configuration.login_id else config.login_id
            config.device_id = configuration.device_id if configuration.device_id else config.device_id
            config.v = configuration.v if configuration.v else config.v
            config.app_version = configuration.app_version if configuration.app_version else config.app_version
            config.request_version = configuration.request_version if configuration.request_version else config.request_version
            config.free_version = configuration.free_version if configuration.free_version else config.free_version
            config.session_id = configuration.session_id if configuration.session_id else config.session_id
            config.uuid = configuration.uuid if configuration.uuid else config.uuid
            db.commit()
            logger.info("修改基本配置信息")
        
        execution.status = "completed"
        execution.result = {"执行结果": "成功"}
        execution.completed_at = datetime.now()
        db.commit()
        
        return config
    except Exception as e:
        execution.status = "failed"
        execution.result = {"error": f"配置更新失败: {str(e)}"}
        execution.completed_at = datetime.now()
        db.commit()
        logger.error(f"配置更新失败: {str(e)}")
        raise HTTPException(status_code=500, detail=f"配置更新失败: {str(e)}")
    
"""
进行ai识别后的后续处理
---
参数:
- ai_result: ai识别结果

返回:
- 200: 返回配置更新结果
"""

def postprocess_ai_cleaned_info(cleaned_info: dict) -> dict:
    """
    对AI清洗后的信息进行再处理。
    参数：
        cleaned_info (dict): AI清洗后的信息。
    返回：
        dict: { 'data': 处理后的字典, 'success': 布尔值 }
    """
    # 示例：假设我们检查'brand'字段是否为空，作为处理逻辑
    processed_info = cleaned_info.copy()
    success = False
    # 声明异常单位列表
    anomalous_unit = ['装', '卡', '砖', '入', '券', '型', '档']
    # 声明基本单位
    base_units = {'KG', 'G', 'ML', 'L', '斤', '公斤', '克', '千克', '升', '毫升'}

    # 数量、基本单位、包装单位处理
    # 处理逻辑1：当包装单位为异常单位时
    if processed_info.get('package_type') in anomalous_unit:
        success = True
        base_unit = processed_info.get('base_unit', '')
        item_specs = processed_info.get('specs', '')
        item_name = processed_info.get('item_name', '')

        # 判断基本单位是否不为异常单位且非空，符合条件则包装单位和基本单位同步
        if base_unit not in anomalous_unit and base_unit:
            processed_info['package_type'] = base_unit
            processed_info['base_num'] = 1.0
        # 当基本单位也是异常单位时的处理
        elif base_unit in anomalous_unit:
            # 判断规格是否包含基本单位，是则通过规格信息获取单位和数量信息
            if base_unit in item_specs:
                idx = item_specs.find(base_unit)
                # 判断数量是否被ai计算过（idx为-1表示被计算过，数量的数值在规格中找不到）
                if idx > 0:
                    text_unit = item_specs[idx -1]

                    processed_info['package_type'] = text_unit
                    processed_info['base_unit'] = text_unit
                    text_num = re.search(r'(\d+)' + text_unit + base_unit, item_specs).group(1)
                    if text_num:
                        processed_info['base_num'] = float(text_num)
                    else:
                        processed_info['base_num'] = 1.0
                else:
                    processed_info['package_type'] = '件'
                    processed_info['base_unit'] = '件'
                    processed_info['base_num'] = 1.0
            # 否则根据商品名称获取单位和数量信息
            else:
                idx = item_name.find(base_unit)
                # 打印idx
                logger.info(f"idx: {idx}")
                if idx > 0:
                    text_unit = item_name[idx-1]
                    processed_info['package_type'] = text_unit
                    processed_info['base_unit'] = text_unit
                    text_num = re.search(r'(\d+)' + text_unit + base_unit, item_name).group(1)
                    # 打印text_num
                    logger.info(f"text_num>>>>>>>>>: {text_num}")
                    if text_num:
                        processed_info['base_num'] = float(text_num)
                    else:
                        processed_info['package_type'] = '件'
                        processed_info['base_unit'] = '件'
                        processed_info['base_num'] = 1.0
                else:
                    processed_info['package_type'] = '件'
                    processed_info['base_unit'] = '件'
                    processed_info['base_num'] = 1.0
        # 若基本单位为空
        else:
            processed_info['package_type'] = '件'
            processed_info['base_unit'] = '件'
            processed_info['base_num'] = 1.0

    # 处理逻辑2：当包装单位非散装，但文本长度大于1时
    elif processed_info.get('package_type') != '散装' and len(processed_info.get('package_type')) > 1:
        success = True
        # 判断基本单位是否为标准单位，是则包装单位为散装
        if processed_info.get('base_unit').upper() in base_units:
            processed_info['package_type'] = '散装'
        # 否则，判断基本单位长度是否等于1，且不为异常单位。是则包装单位与基本单位一致；否则皆默认为件
        elif len(processed_info.get('base_unit')) == 1 and processed_info.get('base_unit') not in anomalous_unit:
            processed_info['package_type'] = processed_info.get('base_unit')
        else:
            processed_info['package_type'] = '件'
            processed_info['base_unit'] = '件'
            processed_info['base_num'] = 1.0

    # 处理逻辑3：当包装单位非散装，但基本单位是标准单位时
    elif processed_info.get('package_type') != '散装' and processed_info.get('base_unit').upper() in base_units:
        success = True
        item_specs = processed_info.get('specs')
        if '*' in item_specs:
            text_specs = item_specs.split('*')[1]
            text_num = float(re.search(r'\d+', text_specs).group(1))
            processed_info['base_num'] = text_num
            text_unit = re.search(r'\d+(.*)', text_specs).group(1)
            if text_unit:
                processed_info['base_unit'] = text_unit
            else:
                processed_info['base_unit'] = processed_info.get('package_type')
        elif 'x' in item_specs:
            text_specs = item_specs.split('x')[1]
            text_num = float(re.search(r'\d+', text_specs).group(1))
            processed_info['base_num'] = text_num
            text_unit = re.search(r'\d+(.*)', text_specs).group(1)
            if text_unit:
                processed_info['base_unit'] = text_unit
            else:
                processed_info['base_unit'] = processed_info.get('package_type')
        elif 'X' in item_specs:
            text_specs = item_specs.split('X')[1]
            text_num = float(re.search(r'\d+', text_specs).group(1))
            processed_info['base_num'] = text_num
            text_unit = re.search(r'\d+(.*)', text_specs).group(1)
            if text_unit:
                processed_info['base_unit'] = text_unit
            else:
                processed_info['base_unit'] = processed_info.get('package_type')
        else:
            processed_info['base_num'] = 1.0
            processed_info['base_unit'] = processed_info.get('package_type')

    # 处理逻辑4：当包装单位为空且基础单位不为空时
    elif not processed_info.get('package_type') and processed_info.get('base_unit'):
        success = True
        if processed_info.get('base_unit').upper() in base_units:
            processed_info['package_type'] = '散装'
        else :
            processed_info['base_num'] = 1.0
            processed_info['package_type'] = processed_info.get('base_unit')

    # 处理逻辑5：当基础单位为空且包装单位不为空时
    elif not processed_info.get('base_unit') and processed_info.get('package_type'):
        success = True
        if processed_info.get('package_type') == '散装':
            processed_info['package_type'] = '件'
        
        processed_info['base_num'] = 1.0
        processed_info['base_unit'] = processed_info.get('package_type')


    # 处理逻辑6：当基本单位为异常单位时(或包装单位非散装情况下长度大于1，亦或长度大于2，再亦或基本单位也为散装)
    elif processed_info.get('base_unit') in anomalous_unit or (processed_info.get('package_type') != '散装' and len(processed_info.get('base_unit')) > 1) or len(processed_info.get('base_unit')) > 2 or processed_info.get('base_unit') == '散装':
        success = True
        # 判断包装单位是否为"散装"。是则根据数量与规格正则；否则同包装单位
        if processed_info.get('package_type') == '散装':
            
            item_specs = processed_info.get('specs')
            base_num = processed_info.get('base_num')
            # 数量是否非空
            if base_num:
                unmatched_information = ''
                # 规格是否非空，决定获取信息的位置
                if item_specs:
                    unmatched_information = item_specs
                else:
                    unmatched_information = processed_info.get('item_name')

                base_unit = re.search(str(base_num) + r'([a-zA-Z]+)',unmatched_information).group(1)
                if base_unit in base_units:
                    processed_info[base_unit] = base_unit
                else:
                    base_unit = re.search(str(base_num) + r'(.)',unmatched_information).group(1)
                    if base_unit and base_unit not in anomalous_unit and base_unit not in {'*', 'x', 'X'}:
                        processed_info[base_unit] = base_unit
                    else:
                        processed_info['package_type'] = '件'
                        processed_info['base_unit'] = '件'
                        processed_info['base_num'] = 1.0
            else:
                processed_info['package_type'] = '件'
                processed_info['base_unit'] = '件'
                processed_info['base_num'] = 1.0
        else:
            processed_info['base_unit'] = processed_info.get('package_type')
            processed_info['base_num'] = 1.0
    
    brand_to_update = {"中国劲酒" : "劲牌"}

    brand_needs_attention = ["海鲜港", "红金龙", "鲁王"]
    is_brand = next((is_brand for is_brand in brand_needs_attention if is_brand in processed_info.get('brand')), None)
    # 商品名称、品牌名称
    # 逻辑1：处理品牌名称为"中国劲酒"的商品
    # 逻辑1：处理品牌名称为需处理品牌的商品
    if processed_info.get('brand') in brand_to_update:
        success = True
        item_name = processed_info.get('item_name')
        item_specs = processed_info.get('specs')

        cleaned_name = item_name.replace(item_specs, '').strip()
        processed_info['cleaned_name'] = cleaned_name
        processed_info['brand'] = brand_to_update.get(processed_info.get('brand'))
        
    # 逻辑2：处理品牌名称带有"海鲜港"的商品
    # 逻辑2：处理品牌名称带有需注意品牌的商品
    elif is_brand:
        success = True
        text_name = processed_info.get('brand').replace(is_brand, '').strip()
        processed_info['cleaned_name'] = text_name + processed_info.get('cleaned_name')
        processed_info['brand'] = is_brand

    # 逻辑3：处理海天品牌的调味料礼盒
    elif '海天' in processed_info.get('brand') and '调味料礼盒' in processed_info.get('item_name'):
        success = True
        item_name = processed_info.get('item_name')
        item_specs = processed_info.get('specs')
        base_unit = processed_info.get('base_unit')
        processed_info['cleaned_name'] = item_name.replace(item_specs, '').replace(base_unit, '').replace('海天', '').replace('-', '').strip()

    # 规格处理
    # 逻辑1：处理带“*”的规格
    if '*' in processed_info.get('specs'):
        item_specs = processed_info.get('specs')
        text_num = item_specs.split('*')[1]
        if str(text_num) == processed_info.get('base_num'):
            success = True
            processed_info['specs'] = item_specs.split('*')[0]
    return {
        'data': processed_info,
        'success': success
    }

"""
SKU测试（不可用）
"""
@router.get("/test/")
def test_sql_sku(
    db: Session = Depends(get_db),
):
    products = db.query(models.ItemSKU).filter().all()
    # 转换为 ProductCreate 所需的 dict
    product_dicts = []
    for p in products:
        product_dicts.append({
            "item_name": p.item_name,
            "item_brand": p.item_brand,
            "item_specs": p.item_specs,
            "item_price": p.item_price,
            "item_classification": p.item_classification,
            "base_price": p.base_price,
            "base_unit": p.base_unit,
            "shop_id": p.shop_id if hasattr(p, "shop_id") else 1,  # 你需要根据实际情况设置
            "shop_name": p.shop_name if hasattr(p, "shop_name") else "",
            "shop_store_id": p.shop_store_id if hasattr(p, "shop_store_id") else 1,
            "sku_id": p.sku,
            "item_img": p.item_img,
            "created_at": p.created_at,
        })
    create_products(product_dicts, db)
    

